﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.Mail
Imports System.Web.HttpContext
Imports log4net
Imports log4net.Config
Imports log4net.Core
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports System.IO
Imports System.Web.Security
Imports System.Drawing
Imports System.Drawing.Text
Imports System.Drawing.Imaging
Imports System.Xml
Imports System.Text
Imports System.Security.Cryptography


Public Class Common
    Public Shared ReadOnly log As ILog = LogManager.GetLogger("TryILogger")
    ' public variables for user information
    Public Shared membershipid As String ' membership UserID
    Public Shared aUserID As Integer ' actual UserID
    Public Shared firstname As String ' first name
    Public Shared lastname As String ' last name
    Public Shared groupid As String ' groupid
    Public Shared address As String ' address
    Public Shared city As String ' city
    Public Shared state As String ' state
    Public Shared stateid As Integer ' state as integer
    Public Shared zipcode As String ' zip
    Public Shared phone As String ' phone
    Public Shared email As String ' email
    Public Shared company As String ' company name
    Public Shared createddate As DateTime ' created date
    Public Shared active As Integer ' active
    Public Shared groupname As String ' groupname
    Public Shared sitecode As String ' sitecode
    Public Shared grouplogo As String ' grouplogo

    ''' <summary>
    ''' Log4Net logging
    ''' </summary>
    ''' <param name="logType"></param>
    ''' <param name="msg"></param>
    ''' <remarks>Used to log Info or Errors on all SUBS/FUNCTIONS</remarks>
    Public Sub Logger(ByVal logType As String, ByVal msg As String)
        Select Case logType
            Case "info"
                log.Info("Training Info * " & msg)
            Case "error"
                log.Error("Training Error * " & msg)
        End Select
    End Sub

    ''' <summary>
    ''' Generic Email Handler
    ''' </summary>
    ''' <param name="eTo"></param>
    ''' <param name="sbj"></param>
    ''' <param name="msg"></param>
    ''' <remarks>Sends email through BRAVO</remarks>
    Public Shared Sub SendEmail(ByVal eTo As String, ByVal sbj As String, ByVal msg As String)
        Dim mail As New System.Net.Mail.MailMessage()

        ' set address
        mail.From = New System.Net.Mail.MailAddress("yourname@yourdomain.com")
        mail.To.Add(eTo)

        ' subject
        mail.Subject = sbj

        ' set body
        mail.Body = msg

        ' set body to be html
        mail.IsBodyHtml = True

        ' send message
        Dim smtp As New System.Net.Mail.SmtpClient("smtphost")
        smtp.Send(mail)

        ' kill mail object
        mail.Dispose()
    End Sub

    ''' <summary>
    ''' Decrypt Querystring
    ''' </summary>
    ''' <param name="stringToDecrypt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function Decrypt(ByVal stringToDecrypt As String) As String
        Dim key() As Byte = {}
        Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
        Dim inputByteArray(stringToDecrypt.Length) As Byte
        Dim sEncryptionKey = ConfigurationManager.AppSettings("encrypter")

        Try
            key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
            Dim des As New DESCryptoServiceProvider()
            inputByteArray = Convert.FromBase64String(stringToDecrypt)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), _
                CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
            Return encoding.GetString(ms.ToArray())
        Catch e As Exception
            Return e.Message
        End Try

    End Function

    ''' <summary>
    ''' Encrypt Querystring
    ''' </summary>
    ''' <param name="stringToEncrypt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function Encrypt(ByVal stringToEncrypt As String) As String
        Dim key() As Byte = {}
        Dim IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
        Dim sEncryptionKey = ConfigurationManager.AppSettings("encrypter")

        Try
            key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
            Dim des As New DESCryptoServiceProvider()
            Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes( _
                stringToEncrypt)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateEncryptor(key, IV), _
                CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Return Convert.ToBase64String(ms.ToArray())
        Catch e As Exception
            Return e.Message
        End Try
    End Function

    ''' <summary>
    ''' check domain to make sure its coming from our server
    ''' </summary>
    ''' <returns>boolean based on domain</returns>
    ''' <remarks></remarks>
    Public Shared Function CheckDomain() As Boolean
        Dim retVal As Boolean
        Dim ref As String ' referrer
        Dim aRef As Array ' array of referrer data

        ref = HttpContext.Current.Request.UrlReferrer.ToString
        ref = Replace(ref, "http://", "")
        aRef = Split(ref, "/")

        If aRef(0) = "yourdomain.com" Then
            retVal = True
        Else
            retVal = False
        End If

        Return retVal
    End Function

#Region "Public" ' code for public section

    ''' <summary>
    ''' Insert New Generic User
    ''' </summary>
    ''' <param name="fname"></param>
    ''' <param name="lname"></param>
    ''' <param name="eml"></param>
    ''' <param name="cmp"></param>
    ''' <param name="adr"></param>
    ''' <param name="cty"></param>
    ''' <param name="ste"></param>
    ''' <param name="zip"></param>
    ''' <param name="phn"></param>
    ''' <returns>Error Code Returned</returns>
    ''' <remarks>0 return value is successful</remarks>
    Public Shared Function AddGenericUser(ByVal fname As String, ByVal lname As String, ByVal eml As String, _
                                        ByVal cmp As String, ByVal adr As String, ByVal cty As String, _
                                        ByVal ste As Integer, ByVal zip As String, _
                                        ByVal phn As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_InsertGenericUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = Trim(fname)
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = Trim(lname)
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = 1
        cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = Trim(cmp)
        If Trim(adr) <> "" Then
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = adr
        Else
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = DBNull.Value
        End If
        If Trim(cty) <> "" Then
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = cty
        Else
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = DBNull.Value
        End If
        If ste <> -1 Then
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = ste
        Else
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = DBNull.Value
        End If
        If Trim(zip) <> "" Then
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = zip
        Else
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = DBNull.Value
        End If
        If Trim(phn) <> "" Then
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = phn
        Else
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = DBNull.Value
        End If
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = Trim(eml)
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get GroupID using logged in users membershipid
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <returns>The groupid of the user</returns>
    ''' <remarks></remarks>
    Public Shared Function GetGroupIDByUserID(ByVal uid As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupIDForUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = New Guid(uid)

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get UserID from Users table by MembershipID
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <returns>Returns integer which is users id</returns>
    ''' <remarks></remarks>
    Public Shared Function GetUserIDByMembershipID(ByVal uid As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetUserIDByMembership")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = New Guid(uid)

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get UserID, GroupID, Group name based on MembershipID
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks>Sets public variables for UserID,GroupID,GroupName</remarks>
    Public Shared Sub GetUserGroupInfo(ByVal uid As String)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetUserGroupInfo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@MembershipID", SqlDbType.UniqueIdentifier).Value = New Guid(uid)

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                aUserID = r("UserID")
                groupid = r("GroupID")
                groupname = r("GroupName")
                grouplogo = r("GroupLogo")
                r.Close()
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub
#End Region

#Region "Admin" ' code for admin section

    ''' <summary>
    ''' Check if GroupName is in use
    ''' </summary>
    ''' <param name="gName"></param>
    ''' <returns>Returns count of GroupName</returns>
    ''' <remarks>Return value of 0 indicated name can be used</remarks>
    Public Shared Function CheckGroupName(ByVal gName As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_CheckGroupName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupName", SqlDbType.NVarChar, 50).Value = Trim(gName)

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Add New Group with UserID of login account
    ''' </summary>
    ''' <param name="gName"></param>
    ''' <param name="uid"></param>
    ''' <returns>Error Code returned</returns>
    ''' <remarks>0 return value is successful</remarks>
    Public Shared Function AddGroupAccount(ByVal gName As String, ByVal uid As String, ByVal logo As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddGroup")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupName", SqlDbType.NVarChar, 50).Value = Trim(gName)
        cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = New Guid(uid)
        If logo = String.Empty Then
            cmd.Parameters.Add("@GroupLogo", SqlDbType.VarChar, 50).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@GroupLogo", SqlDbType.VarChar, 50).Value = logo
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Update Groups with logo information
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <param name="glogo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateGroupLogo(ByVal gid As Integer, ByVal glogo As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateGroupLogo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        cmd.Parameters.Add("@GroupLogo", SqlDbType.VarChar, 50).Value = glogo

        ' inital value of retVal
        retVal = 0

        Try
            con.Open()
            cmd.ExecuteNonQuery()
            retVal = 1
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Check if UserName exists for login creation
    ''' </summary>
    ''' <param name="lName"></param>
    ''' <returns>Returns count of UserName</returns>
    ''' <remarks>0 return value indicates login name is available</remarks>
    Public Shared Function CheckLoginName(ByVal lName As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_CheckLoginName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = Trim(lName)

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Check membership email for existence
    ''' </summary>
    ''' <param name="eml"></param>
    ''' <returns>Returns count of Email in aspnet_membership</returns>
    ''' <remarks>0 indicates email is valid for use</remarks>
    Public Shared Function CheckMemberEmail(ByVal eml As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_CheckMemberEmail")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = Trim(eml)

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Create Login account for Group
    ''' </summary>
    ''' <param name="lname"></param>
    ''' <param name="pwd"></param>
    ''' <param name="eml"></param>
    ''' <returns>MembershipCreateStatus codes returned</returns>
    ''' <remarks></remarks>
    Public Shared Function AddGroupLogin(ByVal lname As String, ByVal pwd As String, ByVal eml As String) As String
        Dim retVal As MembershipCreateStatus

        Try
            Membership.CreateUser(lname, pwd, eml)
        Catch ex As Exception
            log.Error("Group Membership Account Creation ERROR * " & ex.ToString)
        End Try

        Select Case retVal
            Case MembershipCreateStatus.Success
                Return "Account Creation was successful."

            Case MembershipCreateStatus.DuplicateUserName
                Return "Username already exists. Please enter a different user name."

            Case MembershipCreateStatus.DuplicateEmail
                Return "A username for that e-mail address already exists. Please enter a different e-mail address."

            Case MembershipCreateStatus.InvalidPassword
                Return "The password provided is invalid. Please enter a valid password value."

            Case MembershipCreateStatus.InvalidEmail
                Return "The e-mail address provided is invalid. Please check the value and try again."

            Case MembershipCreateStatus.InvalidAnswer
                Return "The password retrieval answer provided is invalid. Please check the value and try again."

            Case MembershipCreateStatus.InvalidQuestion
                Return "The password retrieval question provided is invalid. Please check the value and try again."

            Case MembershipCreateStatus.InvalidUserName
                Return "The user name provided is invalid. Please check the value and try again."

            Case MembershipCreateStatus.ProviderError
                Return "The authentication provider returned an error. Please verify your entry and try again. If the problem persists, please contact your system administrator."

            Case MembershipCreateStatus.UserRejected
                Return "The user creation request has been canceled. Please verify your entry and try again. If the problem persists, please contact your system administrator."

            Case Else
                Return "An unknown error occurred. Please verify your entry and try again. If the problem persists, please contact your system administrator."
        End Select
    End Function

    ''' <summary>
    ''' Change a membership login password
    ''' </summary>
    ''' <param name="unm"></param>
    ''' <param name="pwd"></param>
    ''' <returns>Boolean to determine success</returns>
    ''' <remarks></remarks>
    Public Shared Function ChangeMembershipPassword(ByVal unm As String, ByVal pwd As String) As Boolean
        Dim uName As String = unm
        Dim uPass As String = pwd
        Dim user As MembershipUser
        Dim isValid As Boolean

        ' initial state of boolean is true
        isValid = True

        Try
            ' get user info
            user = Membership.GetUser(uName)

            ' change password
            user.ChangePassword(user.ResetPassword(), uPass)
        Catch ex As Exception
            log.Error("ERROR * Changing Password " & ex.ToString)
            SendEmail("yourname@yourdomain.com", "Training Change Password Error", "Error: " & ex.ToString)
            isValid = False
        End Try

        Return isValid
    End Function

    ''' <summary>
    ''' Add a new User assigned to an existing group
    ''' </summary>
    ''' <param name="muid"></param>
    ''' <param name="fn"></param>
    ''' <param name="ln"></param>
    ''' <param name="gid"></param>
    ''' <param name="eml"></param>
    ''' <param name="act"></param>
    ''' <returns>Returns error code</returns>
    ''' <remarks>Return value of 0 indictates success</remarks>
    Public Shared Function AddGroupUser(ByVal muid As String, ByVal fn As String, ByVal ln As String, _
                                            ByVal gid As Integer, ByVal cnm As String, ByVal adr As String, _
                                            ByVal cty As String, ByVal ste As Integer, ByVal zip As String, _
                                            ByVal phn As String, ByVal eml As String, ByVal scd As String, _
                                            ByVal act As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddGroupUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@MembershipID", SqlDbType.UniqueIdentifier).Value = New Guid(muid)
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = Trim(fn)
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = Trim(ln)
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        If IsNothing(cnm) Then
            cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 150).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 150).Value = cnm
        End If
        If IsNothing(adr) Then
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = adr
        End If
        If IsNothing(cty) Then
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = cty
        End If
        If IsNothing(ste) Then
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = ste
        End If
        If IsNothing(zip) Then
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = zip
        End If
        If IsNothing(phn) Then
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = phn
        End If

        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = Trim(eml)
        If act = 1 Then
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = True
        Else
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = False
        End If

        If IsNothing(scd) Then
            cmd.Parameters.Add("@SiteCode", SqlDbType.NVarChar, 50).Value = DBNull.Value
        Else
            cmd.Parameters.Add("@SiteCode", SqlDbType.NVarChar, 50).Value = scd
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Update user with Membership ID and email, active status
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="muid"></param>
    ''' <param name="eml"></param>
    ''' <param name="act"></param>
    ''' <returns>Returns error code</returns>
    ''' <remarks>Return value of 0 indictates success</remarks>
    Public Shared Function UpdateUserWithMembership(ByVal uid As Integer, ByVal muid As String, _
                            ByVal eml As String, ByVal act As Integer) As Integer

        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateUserForMembership")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@MembershipID", SqlDbType.UniqueIdentifier).Value = New Guid(muid)
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = eml
        If act = 1 Then
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = True
        Else
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = False
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Load User information into public variables for consumption
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub GetUserForReview(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetAccessReviewUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                firstname = r("FirstName")
                lastname = r("LastName")
                If r("Address") Is DBNull.Value Then
                    address = String.Empty
                Else
                    address = r("Address")
                End If
                If r("City") Is DBNull.Value Then
                    city = String.Empty
                Else
                    city = r("City")
                End If
                If r("State") Is DBNull.Value Then
                    state = String.Empty
                Else
                    state = r("State")
                End If
                If r("Zip") Is DBNull.Value Then
                    zipcode = String.Empty
                Else
                    zipcode = r("Zip")
                End If
                If r("Phone") Is DBNull.Value Then
                    phone = String.Empty
                Else
                    phone = r("Phone")
                End If
                email = r("Email")
                company = r("CompanyName")
                createddate = r("CreatedDate")
                r.Close()
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Get User information and load public variables
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub GetUserInfo(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetUserInformation")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                membershipid = r("MembershipID").ToString
                firstname = r("FirstName")
                lastname = r("LastName")
                groupid = r("GroupID")
                If r("Address") Is DBNull.Value Then
                    address = String.Empty
                Else
                    address = r("Address")
                End If
                If r("City") Is DBNull.Value Then
                    city = String.Empty
                Else
                    city = r("City")
                End If
                If r("State") Is DBNull.Value Then
                    stateid = -1
                Else
                    stateid = r("State")
                End If
                If r("Zip") Is DBNull.Value Then
                    zipcode = String.Empty
                Else
                    zipcode = r("Zip")
                End If
                If r("Phone") Is DBNull.Value Then
                    phone = String.Empty
                Else
                    phone = r("Phone")
                End If
                email = r("Email")
                If r("CompanyName") Is DBNull.Value Then
                    company = String.Empty
                Else
                    company = r("CompanyName")
                End If
                createddate = r("CreatedDate")
                If r("SiteCode") Is DBNull.Value Then
                    sitecode = String.Empty
                Else
                    sitecode = r("SiteCode")
                End If
                If r("Active") = True Then
                    active = 1
                Else
                    active = 0
                End If
                r.Close()
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete user who has NOT been given membership login access
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteNonMemberUser(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteUserNoMembership")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            cmd.ExecuteScalar()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Master sub to loop through all users in group and delete data
    ''' and login information for each user based on GroupID
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteGroupUsersAndData(ByVal gid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupUsersInfo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                While r.Read
                    ' loop through users and run other subs
                    DeleteUserQuizModuleData(r("UserID")) ' user quiz & module data
                    Membership.DeleteUser(r("UserName")) ' delete login
                    DeleteUserByID(r("UserID")) ' delete User record
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete Quiz and Module data for individual user
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteUserQuizModuleData(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteQuizModuleDataByUserID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Deletes all data for User in UserCompletedQuizzes and UserQuizAnswers
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteAllUserQuizData(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteAllUserQuizData")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete User record by UserID
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteUserByID(ByVal uid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteUserByID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete Module Refernces for Group
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteGroupModules(ByVal gid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteGroupModuleXRef")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete Membership Login for Group
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteGroupLogin(ByVal gid As Integer)
        Dim uName As String
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupLoginName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            uName = cmd.ExecuteScalar()
            ' delete login
            Membership.DeleteUser(uName)
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Get Login Name for Group by GroupID
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetGroupLogin(ByVal gid As Integer) As String
        Dim uName As String
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupLoginName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        ' initial value of uName
        uName = String.Empty

        Try
            con.Open()
            uName = cmd.ExecuteScalar()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return uName
    End Function

    ''' <summary>
    ''' Get login name for Group User
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetUserLogin(ByVal uid As Integer) As String
        Dim uName As String
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupUserLoginName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid

        ' initial value of uName
        uName = String.Empty

        Try
            con.Open()
            uName = cmd.ExecuteScalar()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return uName
    End Function

    ''' <summary>
    ''' Delete Groups record by GroupID
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteGroupByID(ByVal gid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteGroupByID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Update user information for a Group User
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="fn"></param>
    ''' <param name="ln"></param>
    ''' <param name="gid"></param>
    ''' <param name="adr"></param>
    ''' <param name="cty"></param>
    ''' <param name="ste"></param>
    ''' <param name="zip"></param>
    ''' <param name="phn"></param>
    ''' <param name="eml"></param>
    ''' <param name="act"></param>
    ''' <returns>Returns error code</returns>
    ''' <remarks>Return value of 0 indictates success</remarks>
    Public Shared Function UpdateUserInfo(ByVal uid As Integer, ByVal fn As String, ByVal ln As String, _
                                          ByVal gid As Integer, ByVal adr As String, ByVal cty As String, _
                                          ByVal ste As Integer, ByVal zip As String, ByVal phn As String, _
                                          ByVal eml As String, ByVal scd As String, ByVal act As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateUserInfo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = fn
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = ln
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        If adr <> "" Then
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = adr
        Else
            cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 500).Value = DBNull.Value
        End If
        If cty <> "" Then
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = cty
        Else
            cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = DBNull.Value
        End If
        If ste <> -1 Then
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = ste
        Else
            cmd.Parameters.Add("@State", SqlDbType.Int).Value = DBNull.Value
        End If
        If zip <> "" Then
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = zip
        Else
            cmd.Parameters.Add("@Zip", SqlDbType.NVarChar, 20).Value = DBNull.Value
        End If
        If phn <> "" Then
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = phn
        Else
            cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 20).Value = DBNull.Value
        End If
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = eml
        If act = 1 Then
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = True
        Else
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = False
        End If

        If scd <> "" Then
            cmd.Parameters.Add("@SiteCode", SqlDbType.NVarChar, 50).Value = scd
        Else
            cmd.Parameters.Add("@SiteCode", SqlDbType.NVarChar, 50).Value = DBNull.Value
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get All Current Modules for Selection
    ''' </summary>
    ''' <returns>Returns datatable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetAllModules() As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("ModuleID")
        retVal.Columns.Add("ModuleName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetAllModules")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("ModuleID") = r("ModuleID")
                    myRow("ModuleName") = r("ModuleName")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Lookup GroupID based on Membership UserID
    ''' </summary>
    ''' <param name="muid"></param>
    ''' <returns>Returns integer of GroupID</returns>
    ''' <remarks></remarks>
    Public Shared Function GetGroupIDByMembershipID(ByVal muid As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetGroupIDByMembership")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = New Guid(muid)

        ' inital value of retVal
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Insert Group Module, return error code
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <param name="mdid"></param>
    ''' <returns>Returns error code</returns>
    ''' <remarks>Code of 0 indicates success</remarks>
    Public Shared Function AddGroupModule(ByVal gid As Integer, ByVal mdid As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_InsertGroupModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = mdid
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Update module for group, return error code
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <param name="mdid"></param>
    ''' <returns>Returns error code</returns>
    ''' <remarks>Error code of 0 indicates success</remarks>
    Public Shared Function UpdateGroupModule(ByVal gid As Integer, ByVal mdid As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateGroupModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = mdid
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get All Quizzes
    ''' </summary>
    ''' <returns>Returns datatable of all QuizIDs and QuizNames</returns>
    ''' <remarks></remarks>
    Public Shared Function GetAllQuizzes() As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("QuizID")
        retVal.Columns.Add("QuizName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetAllQuizzes")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim qName As String
                Dim myRow As DataRow
                While r.Read
                    qName = r("QuizName")
                    qName = Replace(qName, "&trade;", "")
                    qName = Replace(qName, "&reg;", "")
                    myRow = retVal.NewRow
                    myRow("QuizID") = r("QuizID")
                    myRow("QuizName") = qName
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get all videos
    ''' </summary>
    ''' <returns>Returns datatable with all VideoID and VideoNames</returns>
    ''' <remarks></remarks>
    Public Shared Function GetAllVideos() As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("VideoID")
        retVal.Columns.Add("VideoName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetAllVideos")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                Dim vName As String
                While r.Read
                    vName = r("VideoName")
                    vName = Replace(vName, "&trade;", "")
                    vName = Replace(vName, "&reg;", "")
                    myRow = retVal.NewRow
                    myRow("VideoID") = r("VideoID")
                    myRow("VideoName") = vName
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Add a new module and return the ID of newly added
    ''' </summary>
    ''' <param name="mname"></param>
    ''' <returns>Returns Integer of new Module</returns>
    ''' <remarks></remarks>
    Public Shared Function AddModuleGetID(ByVal mname As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@ModuleName", SqlDbType.NVarChar, 150).Value = mname

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Add a QuizID for the Module to the ModuleQuizXRef table
    ''' </summary>
    ''' <param name="mdid"></param>
    ''' <param name="qid"></param>
    ''' <remarks></remarks>
    Public Shared Sub AddQuizToModule(ByVal mdid As Integer, ByVal qid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddQuizForModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = mdid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Add a VideoID for the Module to the ModuleVideoXRef table
    ''' </summary>
    ''' <param name="mdid"></param>
    ''' <param name="vid"></param>
    ''' <remarks></remarks>
    Public Shared Sub AddVideoToModule(ByVal mdid As Integer, ByVal vid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddVideoForModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = mdid
        cmd.Parameters.Add("@VideoID", SqlDbType.Int).Value = vid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Update GroupName in Groups table
    ''' </summary>
    ''' <param name="gnm"></param>
    ''' <param name="gid"></param>
    ''' <returns>returns boolean based on error code. Error code of 0 indicates success</returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateGroupName(ByVal gnm As String, ByVal gid As Integer) As Boolean
        Dim retVal As Boolean
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateGroupName")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupName", SqlDbType.NVarChar, 50).Value = gnm
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        retVal = False
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
            iErr = 0
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        If iErr = 0 Then
            retVal = True
        End If

        Return retVal
    End Function

    ''' <summary>
    ''' Get UserID from EMail address
    ''' </summary>
    ''' <param name="eml"></param>
    ''' <remarks></remarks>
    Public Shared Sub GetUserIDByEmail(ByVal eml As String)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetUserIDByEmail")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = eml

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                aUserID = r("UserID")
                firstname = r("FirstName")
                lastname = r("LastName")
                r.Close()
            Else
                firstname = Nothing
                lastname = Nothing
                aUserID = Nothing
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Delete User Results for all Users in Group
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteGroupUserResults(ByVal gid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteGroupUserQuizResults")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Add New Video and return ID
    ''' </summary>
    ''' <param name="vnm"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function AddNewVideo(ByVal vnm As String) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddNewVideo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@VideoName", SqlDbType.NVarChar, 150).Value = vnm

        ' inital value of return value
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Add a new file for collateral material
    ''' </summary>
    ''' <param name="vid"></param>
    ''' <param name="desc"></param>
    ''' <param name="fmt"></param>
    ''' <param name="path"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function AddVideoFile(ByVal vid As Integer, ByVal desc As String, ByVal fmt As String, _
                ByVal path As String) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddNewVideoFile")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@VideoID", SqlDbType.Int).Value = vid
        cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 150).Value = desc
        cmd.Parameters.Add("@Format", SqlDbType.NVarChar, 150).Value = fmt
        cmd.Parameters.Add("@FilePath", SqlDbType.NVarChar, 150).Value = path
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Add New Quiz
    ''' </summary>
    ''' <param name="qnm"></param>
    ''' <param name="pp"></param>
    ''' <param name="cim"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function AddNewQuiz(ByVal qnm As String, ByVal pp As Integer, ByVal cim As String) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddNewQuiz")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizName", SqlDbType.NVarChar, 120).Value = qnm
        cmd.Parameters.Add("@PassPercentage", SqlDbType.Int).Value = pp
        cmd.Parameters.Add("@CertificateImage", SqlDbType.NVarChar, 256).Value = cim
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Delete Quiz and all related items
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteQuiz(ByVal qid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteQuizAndAllRelated")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            cmd.ExecuteScalar()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' update quiz information
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <param name="qnm"></param>
    ''' <param name="pp"></param>
    ''' <param name="cim"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateQuizInfo(ByVal qid As Integer, ByVal qnm As String, ByVal pp As Integer, ByVal cim As String) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateQuiz")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@QuizName", SqlDbType.NVarChar, 120).Value = qnm
        cmd.Parameters.Add("@PassPercentage", SqlDbType.Int).Value = pp
        cmd.Parameters.Add("@CertificateImage", SqlDbType.NVarChar, 256).Value = cim
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Insert Quiz Question Data
    ''' </summary>
    ''' <param name="q"></param>
    ''' <param name="img"></param>
    ''' <param name="plc"></param>
    ''' <returns>error code</returns>
    ''' <remarks>image and placement may be null</remarks>
    Public Shared Function AddNewQuestionData(ByVal q As String, ByVal img As String, ByVal plc As Integer) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_InsertQuestionData")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 1500).Value = q
        If img <> "" Then
            cmd.Parameters.Add("@QuestionImage", SqlDbType.NVarChar, 256).Value = img
        Else
            cmd.Parameters.Add("@QuestionImage", SqlDbType.NVarChar, 256).Value = DBNull.Value
        End If
        If plc = 1 Or plc = 0 Then
            cmd.Parameters.Add("@ImagePlacement", SqlDbType.Int).Value = plc
        Else
            cmd.Parameters.Add("@ImagePlacement", SqlDbType.Int).Value = DBNull.Value
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Get Question Information
    ''' </summary>
    ''' <param name="qsid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetQuestionData(ByVal qsid As Integer) As Array
        Dim arQuesd(2) As String
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuestionData")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                arQuesd(0) = r("Question")
                If r("QuestionImage") Is DBNull.Value Then
                    arQuesd(1) = String.Empty
                Else
                    arQuesd(1) = r("QuestionImage")
                End If
                If r("ImagePlacement") Is DBNull.Value Then
                    arQuesd(2) = String.Empty
                Else
                    arQuesd(2) = r("ImagePlacement")
                End If
                r.Close()
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return arQuesd
    End Function

    ''' <summary>
    ''' Update Question Data
    ''' </summary>
    ''' <param name="qsid"></param>
    ''' <param name="q"></param>
    ''' <param name="img"></param>
    ''' <param name="plc"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateQuestionData(ByVal qsid As Integer, ByVal q As String, ByVal img As String, ByVal plc As Integer) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateQuestionData")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid
        cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 1500).Value = q
        If img <> "" Then
            cmd.Parameters.Add("@QuestionImage", SqlDbType.NVarChar, 256).Value = img
        Else
            cmd.Parameters.Add("@QuestionImage", SqlDbType.NVarChar, 256).Value = DBNull.Value
        End If
        If plc = 1 Or plc = 0 Then
            cmd.Parameters.Add("@ImagePlacement", SqlDbType.Int).Value = plc
        Else
            cmd.Parameters.Add("@ImagePlacement", SqlDbType.Int).Value = DBNull.Value
        End If
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Delete Quiz Question
    ''' </summary>
    ''' <param name="qsid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function DeleteQuestionByID(ByVal qsid As Integer) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteQuizQuestion")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Insert New Answer
    ''' </summary>
    ''' <param name="ans"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function InsertNewAnswer(ByVal ans As String) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_InsertNewAnswer")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@Answer", SqlDbType.NVarChar, 150).Value = ans
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Update QuizAnswers
    ''' </summary>
    ''' <param name="aid"></param>
    ''' <param name="ans"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function UpdateQuizAnswer(ByVal aid As Integer, ByVal ans As String) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_UpdateAnswer")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@AnswerID", SqlDbType.Int).Value = aid
        cmd.Parameters.Add("@Answer", SqlDbType.NVarChar, 150).Value = ans
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Delete QuizAnswer
    ''' </summary>
    ''' <param name="aid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteQuizAnswer(ByVal aid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteAnswer")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@AnswerID", SqlDbType.Int).Value = aid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Insert New Question/Answer combination for quiz
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <param name="qsid"></param>
    ''' <param name="aid"></param>
    ''' <param name="ca"></param>
    ''' <param name="aod"></param>
    ''' <param name="qod"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function InsertQuizQAXRef(ByVal qid As Integer, ByVal qsid As Integer, ByVal aid As Integer, _
                ByVal ca As Boolean, ByVal aod As Integer, ByVal qod As Integer) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_AddQuizQAXRef")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid
        cmd.Parameters.Add("@AnswerID", SqlDbType.Int).Value = aid
        cmd.Parameters.Add("@CorrectAnswer", SqlDbType.Bit).Value = ca
        cmd.Parameters.Add("@AnswerOrder", SqlDbType.Int).Value = aod
        cmd.Parameters.Add("@QuestionOrder", SqlDbType.Int).Value = qod
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Delete XRef for Quiz Question
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <param name="qsid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function DeleteXRefQuizQuest(ByVal qid As Integer, ByVal qsid As Integer) As Integer
        Dim iErr As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteXRefQuestionForQuiz")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid
        cmd.Parameters.Add("@iError", SqlDbType.Int).Direction = ParameterDirection.Output

        ' inital value of retval/iErr
        iErr = 0

        Try
            con.Open()
            iErr = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return iErr
    End Function

    ''' <summary>
    ''' Delete Quiz Question/Answer XRef
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <param name="qsid"></param>
    ''' <param name="aid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteQuizAnsQuesXRef(ByVal qid As Integer, ByVal qsid As Integer, ByVal aid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteAnswerXRefForQuizQuestion")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsid
        cmd.Parameters.Add("@AnswerID", SqlDbType.Int).Value = aid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub
#End Region

#Region "Training"
    ''' <summary>
    ''' pulls back an array list of the moduleid and modulename
    ''' </summary>
    ''' <param name="gid"></param>
    ''' <returns>Returns Datatable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetModulesForGroup(ByVal gid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("ModuleID")
        retVal.Columns.Add("ModuleName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetModulesForGroup")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = gid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("ModuleID") = r("ModuleID")
                    myRow("ModuleName") = r("ModuleName")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal

    End Function

    ''' <summary>
    ''' Get QuizIDs and QuizNames for module by ModuleID
    ''' </summary>
    ''' <param name="modid"></param>
    ''' <returns>Return Datatable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetModuleQuizzes(ByVal modid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("QuizID")
        retVal.Columns.Add("QuizName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuizzesForModule")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = modid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("QuizID") = r("QuizID")
                    myRow("QuizName") = r("QuizName")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get VideoIDs and VideoNames for module by ModuleID
    ''' </summary>
    ''' <param name="modid"></param>
    ''' <returns>Returns DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetModuleVideos(ByVal modid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("VideoID")
        retVal.Columns.Add("VideoName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetVideoNamesByModuleID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@ModuleID", SqlDbType.Int).Value = modid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("VideoID") = r("VideoID")
                    myRow("VideoName") = r("VideoName")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Gets all questions for a particular Quiz by QuizID
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <returns>Returns DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetQuizQuestionsByQuizID(ByVal qid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("QuestionID")
        retVal.Columns.Add("Question")
        retVal.Columns.Add("QuestionImage")
        retVal.Columns.Add("ImagePlacement")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuizQuestionsByQuizID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("QuestionID") = r("QuestionID")
                    myRow("Question") = r("Question")
                    myRow("QuestionImage") = r("QuestionImage")
                    myRow("ImagePlacement") = r("ImagePlacement")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get Video Files for Video Modules base on VideoID
    ''' </summary>
    ''' <param name="vid"></param>
    ''' <returns>Returns DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetVideoFilesForVideo(ByVal vid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("VideoFileID")
        retVal.Columns.Add("Description")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetVideoFilesByVideoID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@VideoID", SqlDbType.Int).Value = vid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("VideoFileID") = r("VideoFileID")
                    myRow("Description") = r("Description")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get answers to question by QuestionID
    ''' </summary>
    ''' <param name="qsd"></param>
    ''' <param name="qid"></param>
    ''' <returns>Returns DataTable</returns>
    ''' <remarks></remarks>
    Public Shared Function GetQuizQuestiionAnswersByQID(ByVal qsd As Integer, ByVal qid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("AnswerID")
        retVal.Columns.Add("Answer")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuizQuestionAnswersByQID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsd
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("AnswerID") = r("AnswerID")
                    myRow("Answer") = r("Answer")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get Correct answerid for question by questionid
    ''' </summary>
    ''' <param name="qsd"></param>
    ''' <returns>Returns integer of the AnswerID which is the correct answer to a question</returns>
    ''' <remarks></remarks>
    Public Shared Function GetCorrectAnswerToQuestion(ByVal qsd As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetCorrectAnswerIDForQuestion")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsd

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Check users response to a question against the correct answer
    ''' </summary>
    ''' <param name="qsd"></param>
    ''' <param name="aid"></param>
    ''' <returns>Return true if users answer matches correct answerid</returns>
    ''' <remarks></remarks>
    Public Shared Function CheckUserQuestionResponse(ByVal qsd As Integer, ByVal aid As Integer) As Boolean
        Dim retVal As Boolean

        If GetCorrectAnswerToQuestion(qsd) = aid Then
            retVal = True
        Else
            retVal = False
        End If

        Return retVal
    End Function

    ''' <summary>
    ''' Inserts User Response to Quiz Question
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <param name="qsd"></param>
    ''' <param name="aid"></param>
    ''' <param name="ca"></param>
    ''' <remarks></remarks>
    Public Shared Sub InsertUserQuizQuestionResponse(ByVal uid As Integer, ByVal qid As Integer, _
                                                          ByVal qsd As Integer, ByVal aid As Integer, _
                                                          ByVal ca As Boolean)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_InsertUserQuizQuestionResponses")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsd
        cmd.Parameters.Add("@AnswerID", SqlDbType.Int).Value = aid
        cmd.Parameters.Add("@CorrectAnswer", SqlDbType.Bit).Value = ca

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Mark the Quiz Complete for the user, record PASS/FAIL
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <param name="p"></param>
    ''' <remarks></remarks>
    Public Shared Sub MarkQuizCompleteForUser(ByVal uid As Integer, ByVal qid As Integer, ByVal p As Boolean)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_MarkQuizCompleteForUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid
        cmd.Parameters.Add("@Passed", SqlDbType.Bit).Value = p

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Check for completed Quiz for user
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <returns>Returns boolean, true if user has taken quiz</returns>
    ''' <remarks></remarks>
    Public Shared Function CheckCompletedQuizForUser(ByVal uid As Integer, ByVal qid As Integer) As Boolean
        Dim retVal As Boolean
        Dim rCount As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_CheckCompletedQuizForUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        ' initialize variables
        rCount = 0
        retVal = False

        Try
            con.Open()
            rCount = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        ' check count for completed
        If rCount > 0 Then
            retVal = True
        End If

        Return retVal
    End Function

    ''' <summary>
    ''' Get number of questions and passing percentage for quiz
    ''' calculate number of quests needed correct to pass
    ''' </summary>
    ''' <param name="qid"></param>
    ''' <returns>Returns double of number of questions needed to pass quiz</returns>
    ''' <remarks></remarks>
    Public Shared Function GetPassingPercentage(ByVal qid As Integer) As Double
        Dim retVal As Double
        Dim numQues As Integer
        Dim pct As Integer

        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuizPassPercentage")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        ' initial value of retVal
        retVal = 0

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                numQues = r("NumQuestions")
                pct = r("PCT")
                r.Close()
            End If

            ' calculate number of questions needed correct to pass
            retVal = (numQues * (pct / 100))
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Get count of users correct responses to quiz by UserID and QuizID
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <returns>Return integer with count of correct responses</returns>
    ''' <remarks></remarks>
    Public Shared Function GetCountUserCorrectQuizResponses(ByVal uid As Integer, ByVal qid As Integer) As Integer
        Dim retVal As Integer
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetUserCorrectQuizResponses")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        ' inital value of return val
        retVal = 0

        Try
            con.Open()
            retVal = cmd.ExecuteScalar
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Check values for quiz and user responses to determine if user passed
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <returns>Return boolean TRUE if user passed</returns>
    ''' <remarks></remarks>
    Public Shared Function CheckUserQuizPassing(ByVal uid As Integer, ByVal qid As Integer) As Boolean
        Dim retVal As Boolean
        Dim psNum As Double ' number of questions correct needed to pass quiz
        Dim crNum As Double ' number of correct answer user had for quiz

        ' initial value of return value
        retVal = False

        ' get number questions needed to pass
        psNum = GetPassingPercentage(qid)

        ' get number of users correct responses
        crNum = GetCountUserCorrectQuizResponses(uid, qid)

        ' check if user passed quiz
        If crNum >= psNum Then
            retVal = True
        End If

        Return retVal
    End Function

    ''' <summary>
    ''' Returns table of missed questions for user
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function MissedQuestions(ByVal uid As Integer, ByVal qid As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("QuestionID")
        retVal.Columns.Add("Question")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetIncorrectQuizQuestions")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("QuestionID") = r("QuestionID")
                    myRow("Question") = r("Question")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Gets information to display a video by VideoFileID
    ''' </summary>
    ''' <param name="vif"></param>
    ''' <returns></returns>
    ''' <remarks>Returns DataTable</remarks>
    Public Shared Function GetVideoInfo(ByVal vif As Integer) As DataTable
        Dim retVal As DataTable = New DataTable
        retVal.Columns.Add("VideoID")
        retVal.Columns.Add("Description")
        retVal.Columns.Add("Format")
        retVal.Columns.Add("FilePath")
        retVal.Columns.Add("VideoName")
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetVideoFileInformation")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@VideoFileID", SqlDbType.Int).Value = vif

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                Dim myRow As DataRow
                While r.Read
                    myRow = retVal.NewRow
                    myRow("VideoID") = r("VideoID")
                    myRow("Description") = r("Description")
                    myRow("Format") = r("Format")
                    myRow("FilePath") = r("FilePath")
                    myRow("VideoName") = r("VideoName")
                    retVal.Rows.Add(myRow)
                End While
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

    ''' <summary>
    ''' Generate certificate for completed quiz
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <returns>return image name of created image</returns>
    ''' <remarks></remarks>
    Public Shared Function CreateCertificate(ByVal uid As Integer, ByVal qid As Integer) As String
        Dim retVal As String
        Dim uName As String ' User Name
        Dim cDte As DateTime ' Completed Date
        Dim qName As String ' Quiz Name
        Dim cert As String ' certificateimage name
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetCertificateInfo")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        ' set initial values
        uName = String.Empty
        cDte = Now()
        qName = String.Empty
        cert = String.Empty

        Try
            con.Open()
            Dim r As SqlDataReader = cmd.ExecuteReader
            If r.HasRows Then
                r.Read()
                uName = r("FullName")
                cDte = r("CompletedDate")
                qName = r("QuizName")
                cert = r("CertificateImage")
                r.Close()
            End If
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Dim path As String = System.Web.HttpContext.Current.Server.MapPath("images/") ' path to images directory
        Dim oBitmap As Bitmap = New Bitmap(path & cert)
        Dim oGraphic As Graphics = Graphics.FromImage(oBitmap)
        Dim oColor As System.Drawing.Color

        ' Users Name
        Dim sText As String = uName
        Dim sFont As String = "Times New Roman"

        Dim oBrush As New SolidBrush(oColor)
        Dim oBrushWrite As New SolidBrush(System.Drawing.Color.Red)

        Dim oFont As New System.Drawing.Font(sFont, 36)
        Dim oPoint As New PointF(600, 490)

        ' Date of Completion
        Dim dText As String = FormatDateTime(cDte, DateFormat.ShortDate)

        Dim dBrush As New SolidBrush(oColor)
        Dim dBrushWrite As New SolidBrush(System.Drawing.Color.Black)

        Dim dFont As New System.Drawing.Font("Arial", 19)
        Dim dPoint As New PointF(820, 738)

        ' name of quiz completed
        'qName = Replace(qName, "&trade;", "") ' clean html entity
        'qName = Replace(qName, "&reg;", "") ' ' clean html entity
        'Dim cText As String = "Completed " & qName & " Training"

        'Dim cBrush As New SolidBrush(oColor)
        'Dim cBrushWrite As New SolidBrush(Color.DarkBlue)

        'Dim cFont As New Font("Arial", 32)
        'Dim cPoint As New PointF(600, 480)

        ' graphic
        oGraphic.FillRectangle(oBrush, 0, 0, 468, 60)
        oGraphic.TextRenderingHint = TextRenderingHint.AntiAlias

        ' alignment
        Dim sf As New StringFormat
        sf.Alignment = StringAlignment.Center

        ' generate text on graphic
        oGraphic.DrawString(sText, oFont, oBrushWrite, oPoint, sf) ' username
        oGraphic.DrawString(dText, dFont, dBrushWrite, dPoint) ' date
        'oGraphic.DrawString(cText, cFont, cBrushWrite, cPoint, sf) ' completed quiz

        ' save certificate image as jpg
        Dim cImage As String = uid & "_" & qid & "_" & "cert_img.jpg"
        Try
            oBitmap.Save(System.Web.HttpContext.Current.Server.MapPath("certs/" & cImage), ImageFormat.Jpeg)
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        End Try

        ' set return value to be certificate image
        retVal = cImage
        Return retVal
    End Function

    ''' <summary>
    ''' Create a PDF from the Cert image
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <param name="cImage"></param>
    ''' <returns>String to PDF with Cert Image</returns>
    ''' <remarks></remarks>
    Public Shared Function CreatePDFCert(ByVal uid As Integer, ByVal qid As Integer, ByVal cImage As String) As String
        ' create pdf from jpg
        Dim rect As iTextSharp.text.Rectangle
        rect = New iTextSharp.text.Rectangle(0, 30, 1250, 1027)
        Dim doc As Document = New Document(rect)
        Dim nPath As String = System.Web.HttpContext.Current.Server.MapPath("certs/")
        Dim pImage As String ' pdf filename
        pImage = uid & "_" & qid & "_" & "cert.pdf"

        Try
            PdfWriter.GetInstance(doc, New FileStream(nPath & pImage, FileMode.Create))
            doc.Open()

            ' use jpg cert image to add to pdf
            Dim jpeg As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(nPath & cImage)
            doc.Add(jpeg)

            doc.Close()
            pImage = uid & "_" & qid & "_" & "cert.pdf"
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        End Try

        Return pImage
    End Function

    ''' <summary>
    ''' Delete User QuizData to allow user to retake quiz
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <param name="qid"></param>
    ''' <remarks></remarks>
    Public Shared Sub DeleteUserQuizData(ByVal uid As Integer, ByVal qid As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_DeleteQuizResultsForUser")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = uid
        cmd.Parameters.Add("@QuizID", SqlDbType.Int).Value = qid

        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    ''' <summary>
    ''' Get Question text by the QuestionID
    ''' </summary>
    ''' <param name="qsd"></param>
    ''' <returns>Returns string of Question text</returns>
    ''' <remarks></remarks>
    Public Shared Function GetQuestionTextByQuestionID(ByVal qsd As Integer) As String
        Dim retVal As String
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim cmd As New SqlCommand("proc_GetQuizQuestionByQuestionID")
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con
        cmd.Parameters.Add("@QuestionID", SqlDbType.Int).Value = qsd

        ' inital value of retVal
        retVal = String.Empty

        Try
            con.Open()
            retVal = cmd.ExecuteScalar()
        Catch ex As Exception
            log.Error("ERROR * " & ex.ToString)
        Finally
            con.Close()
            cmd.Dispose()
        End Try

        Return retVal
    End Function

#End Region
End Class


